
import openpyxl
from openpyxl import Workbook 
import re
import os
import json
import ability_yingli as YL_NL
import ability_changzhai as CZ_NL


PROJECT_PATH = os.path.dirname(__file__)

class Report():

    def __init__(self, file_name):
        self.gross_profit_margin = []
        self.filter_codes = []
        self.year_num = None
        self.wb = Workbook()
        self.save_file = os.path.join(PROJECT_PATH, file_name)
        ws1 = self.wb.active
        ws1.title = "首页"

    def get_json_data(self, json_path):
        with open(json_path, 'r', encoding='utf-8') as file:
            data = json.load(file)
        self.year_num = data['year_num']
        return data

    def read_xlsx_data(self, path):
        print("======*****************======")
        workbook = openpyxl.load_workbook(path)
        sheet = workbook.active
        # cell_value = sheet['A1'].value
        
        # row1_values = [cell.value for cell in sheet[1]]
 
        # print(row1_values)
        # print("===========================")
        # column1_values = []
        # for i in range(1, sheet.max_row + 1):
        #     # print(repr(sheet[f'A{i}'].value))
        #     A_value = sheet[f'A{i}'].value
        #     if A_value and re.search(r".*\d+.*", A_value):
        #         if A_value not in self.filter_codes:
        #             column1_values.append(sheet[f'A{i}'].value)

        # # for column in column1_values:  
        # #     print(column)

        # print(len(column1_values))
        
        # print("===========================")  
        # 遍历所有单元格数据  
        iter_rows = sheet.iter_rows(values_only=True)
        rows_list = list(iter_rows)
        for row in rows_list:  
            print(row)

        data_list = []
        for i in range(len(rows_list)):
            # 如果当前行是标题行或数据为空，则跳过
            if (rows_list[i][0] is None) or ("数据来源" in rows_list[i][0]):
                continue
            
            data_list.append(rows_list[i])

        for row in data_list:  
            print(row)

        # 按照证券号码排序，为后续计算方便准备。
        sorted_data = [data_list[0]] + sorted(data_list[1:], key=lambda x: x[0])
        for item in sorted_data:
            print(item)

        recent_year_datas = self.get_recent_n_years_data(sorted_data, self.year_num)
        return recent_year_datas
    
    def get_recent_n_years_data(self, data, year_num=None):
        if year_num is None:
            return data
        # 提取年份信息
        years = [re.search(r'\d{4}', item).group() for item in data[0][2:] if re.search(r'\d{4}', item)]
        print(years)
        # 确定最近5年
        recent_years = sorted(years, key=lambda x: int(x))[-year_num:]
        print(recent_years)
        # 初始化一个列表来存储最近5年的数据
        recent_data = []

        # 遍历数据，提取最近5年的数据
        for row in data[0:]:
            if row[1] is None:  # 跳过空行
                continue
            recent_row = [row[0], row[1]]  # 证券代码和名称
            for year in recent_years:
                for index, item in enumerate(data[0][2:]):
                    if str(year) in item:
                        recent_row.append(row[index + 2])
            recent_data.append(recent_row)

        # 打印最近5年的数据
        for row in recent_data:
            print(row)

        return recent_data

    def get_valid_title(self, data1, data2):
        list1 = data1[0]
        list2 = data2[0]
        if len(list1) != len(list2):
            print("Error: 两个表中年份长度不一致")
            return 
        
        if list1[0] != list2[0] or list1[1] != list2[1]:
            print("Error: 两个数据名称不一致")
            return

        if len(data1) != len(data2):
            print("Error: 两个表中对应的证券数不一致")
            return

        titles = [list1[0], list1[1]]
        
        for i in range(2, len(list1)):
            temp1_i = list1[i].replace("\n", "")
            temp2_i = list2[i].replace("\n", "")
            year1, unit1 = self.tilte_match(temp1_i)
            year2, unit2 = self.tilte_match(temp2_i)
                
            if year1 != year2:
                print(f"Error: 年份不一致：{temp1_i} and {temp2_i}.")
                return False

            if unit1 != unit2:
                print(f"Error: 计算单位不一致 {temp1_i} and {temp2_i}.")
                return False
            
            titles.append(year1)
        return titles

    def tilte_match(self, strpara):
        print("###########################")
        reg = r"(\d{4}年).*单位\](.*元)"
        match1 = re.search(reg, strpara, re.DOTALL)
        if match1:
            year = match1.group(1)
            unit = match1.group(2)
            # print(year, unit)
            return year, unit
        else:
            print("名称字段匹配失败")

    def wirte_result(self, name, index, datalist):
        # 每个workbook创建后，默认会存在一个worksheet，对默认的worksheet进行重命名
        # worksheet.title = name
        worksheet = self.wb.create_sheet(name, index)
        for row in datalist:
            worksheet.append(row)

    def save_report(self):
        self.wb.save(self.save_file)

    def get_norank_rst_data(self, fen_mu_path, fen_zi_path, formula):
        data_list1 = self.read_xlsx_data(fen_mu_path)
        data_list2 = self.read_xlsx_data(fen_zi_path)
        
        title = self.get_valid_title(data_list1, data_list2)
        # 初始化结果列表
        first_calc_data = []
        first_calc_data.append(title)
        # 遍历data_list1和list2的每一行
        for i in range(len(data_list1)):
            # 如果当前行是标题行或数据为空，则跳过
            if (data_list1[i][0] is None) or ('证券代码' in data_list1[i][0]) or ("数据来源" in data_list1[i][0]):
                continue
            
            # 创建新的行，包含编号和名称
            new_row = [data_list1[i][0], data_list1[i][1]]
            # 由于开始进行了按照号码排序，并且判断了每个表格的行数，所以这样可以直接比较，不需要循环。
            if data_list1[i][0] == data_list2[i][0]:
                # 遍历data_list1和list2的每一列（除第一列和第二列外）
                for j in range(2, len(data_list1[i])):  # j表示第一个数组的列 
                    if data_list1[i][j] is not None and data_list2[i][j] is not None:
                        value = formula(data_list1[i][j], data_list2[i][j])   # 计算公式
                        new_row.append(value)
                    else:
                        new_row.append(None)
            else:
                print("error--3个参数中的同一行证券代码不一致")

            # for i2 in range(1, len(data_list2)):
            #     if data_list1[i][0] != data_list2[i2][0]:
            #         continue

            #     # 遍历data_list1和list2的每一列（除第一列和第二列外）
            #     for j in range(2, len(data_list1[i])):  # j表示第一个数组的列 
            #         if data_list1[i][j] is not None and data_list2[i2][j] is not None:
            #             value = formula(data_list1[i2][j], data_list2[i][j])   # 计算公式
            #             new_row.append(value)
            #         else:
            #             new_row.append(None)
            
            # 将新的行添加到结果列表中
            first_calc_data.append(new_row)

        # 打印结果列表
        print("+++++++++++++++++++++++++++++++++++++++++++")
        for row in first_calc_data:
            print(row) 

        return first_calc_data

    def get_norank_rst_for_mao_jing_cha(self, z_ying_shou, cheng_ben, jing_li_rong, formula):
        data_list1 = self.read_xlsx_data(z_ying_shou)
        data_list2 = self.read_xlsx_data(cheng_ben)
        data_list3 = self.read_xlsx_data(jing_li_rong)
        
        title = self.get_valid_title(data_list1, data_list2)
        title2 = self.get_valid_title(data_list1, data_list3)
        # 初始化结果列表
        first_calc_data = []
        first_calc_data.append(title)
        # 遍历data_list1和list2的每一行  i 表示行
        for i in range(len(data_list1)):
            # 如果当前行是标题行或数据为空，则跳过
            if (data_list1[i][0] is None) or ('证券代码' in data_list1[i][0]) or ("数据来源" in data_list1[i][0]):
                continue
            
            new_row = [data_list1[i][0], data_list1[i][1]]
            
            # 由于开始进行了按照号码排序，并且判断了每个表格的行数，所以这样可以直接比较，不需要循环。
            if (data_list1[i][0] == data_list2[i][0]) and (data_list1[i][0] == data_list3[i][0]):
                # 遍历data_list1和list2的每一列（除第一列和第二列外）
                for j in range(2, len(data_list1[i])):  # j表示第一个数组的列 
                    if data_list1[i][j] is not None and data_list2[i][j] is not None:
                        value = formula(data_list1[i][j], data_list2[i][j], data_list3[i][j])   # 计算公式
                        new_row.append(value)
                    else:
                        new_row.append(None)
            else:
                print("error--3个参数中的同一行证券代码不一致")
            
            # 将新的行添加到结果列表中
            first_calc_data.append(new_row)

        # 打印结果列表
        print("+++++++++++++++++++++++++++++++++++++++++++")
        for row in first_calc_data:
            print(row) 

        return first_calc_data
    
    def get_data_after_rank(self, data, sort=True):
        '''把每年的排名添加到原来数组之后'''
        years = data[0][2:]
        for year in years:
            rank_label = f"{year}排名"
            data[0].append(rank_label)

        # 对每一年的数据进行处理
        for year_index, year in enumerate(years, start=2):
            # 提取每一年的所有证券数据
            yearly_data = [row[year_index] for row in data[1:]]
            print("===========11111111111")
            print(yearly_data)
            # 计算每个证券在该年的排名
            sorted_values = sorted(yearly_data, reverse=sort)
            rankings = {value: rank for rank, value in enumerate(sorted_values, start=1)}
            ranks = []
            for value in yearly_data:
                # print(f"{value} 的排名是: {rankings[value]}")
                ranks.append(rankings[value])
            print(ranks)
            # 将排名添加到对应的证券数据列表的末尾
            for security_index, rank in enumerate(ranks):
                # print(security_index, rank)
                data[security_index + 1].append(rank)

        # 打印结果
        for row in data:
            print(row)

        return data
    
    def get_data_after_final_rank(self, data):
        '''计算综合排名，并且按照综合排名排序'''
        first_rank_index = 0
        for index, year in enumerate(data[0]):
            if "排名" in year:
                first_rank_index = index   # 获取第一个排名index
                break
        print(f"第一个排名index:{first_rank_index}")
        # 添加综合排名的标题
        data[0].append('综合排名')

        # 计算综合排名
        for i in range(1, len(data)):
            # 获取该证券的排名数据
            rankings = data[i][first_rank_index:]  # 排名数据从第一个排名开始
            # print(rankings)
            # 计算平均排名，排除非数字值
            average_rank = sum(int(ranking) for ranking in rankings) / len(rankings)
            # 四舍五入保留两位小数
            average_rank = round(average_rank, 1)
            # 添加平均排名到数据行的末尾
            data[i].append(average_rank)

        # for row in data:
        #     print(row)

        # 按照综合排名排序
        sorted_data = sorted(data[1:], key=lambda x: float(x[-1]))

        # 将标题行添加回排序后的数据
        sorted_data.insert(0, data[0])

        # 打印排序后的结果
        for row in sorted_data:
            print(row)
    
        return sorted_data
    
    def get_analysis_result(self, fen_mu_file, fen_zi_file, formula, sort=True):
        fen_mu_path = os.path.join(PROJECT_PATH, 'original_file', fen_mu_file)
        fen_zi_path = os.path.join(PROJECT_PATH, 'original_file', fen_zi_file)

        first_calc_data = self.get_norank_rst_data(fen_mu_path, fen_zi_path, formula)
        year_rank_data = self.get_data_after_rank(first_calc_data, sort)
        final_rank_data = self.get_data_after_final_rank(year_rank_data)
        return final_rank_data
    
    def get_mao_jing_cha(self, z_ying_shou, cheng_ben, g_jing_li_r, formula):
        data1 = os.path.join(PROJECT_PATH, 'original_file', z_ying_shou)
        data2 = os.path.join(PROJECT_PATH, 'original_file', cheng_ben)
        data3 = os.path.join(PROJECT_PATH, 'original_file', g_jing_li_r)

        first_calc_data = self.get_norank_rst_for_mao_jing_cha(data1, data2, data3, formula)
        year_rank_data = self.get_data_after_rank(first_calc_data, False)
        final_rank_data = self.get_data_after_final_rank(year_rank_data)
        return final_rank_data





if __name__ == "__main__":
    path1 = os.path.join(PROJECT_PATH, 'original_file', '白酒营业总收入.xlsx')
    path2 = os.path.join(PROJECT_PATH, 'original_file', '白酒营业成本.xlsx')
    json_path = os.path.join(PROJECT_PATH, 'original_file', 'CFG_INFO.json')
    report = Report("白酒盈利能力.xlsx")
    CFG = report.get_json_data(json_path)
    datalist = report.get_analysis_result(CFG['营业总收入'], CFG['营业成本'], YL_NL.calc_mao_li_lv)
    report.wirte_result("毛利率", 1,  datalist)
    datalist = report.get_analysis_result(CFG['营业总收入'], CFG['归母净利润'], YL_NL.calc_jing_li_lv)
    report.wirte_result("净利率", 2,  datalist)
    datalist = report.get_mao_jing_cha(CFG['营业总收入'], CFG['营业成本'], CFG['归母净利润'], YL_NL.calc_mao_jing_cha)
    report.wirte_result("毛净差", 3,  datalist)
    datalist = report.get_analysis_result(CFG['总资产'], CFG['归母净利润'], YL_NL.calc_ROA)
    report.wirte_result("总资产收益率ROA", 4,  datalist)
    datalist = report.get_analysis_result(CFG['归母净资产'], CFG['归母净利润'], YL_NL.calc_ROE)
    report.wirte_result("净资产收益率ROE", 5,  datalist)

    report = Report("白酒偿债能力.xlsx")
    CFG = report.get_json_data(json_path)
    datalist = report.get_analysis_result(CFG['总资产'], CFG['总负债'], CZ_NL.calc_zi_chang_fu_zhai_lv)
    report.wirte_result("资产负债率", 1,  datalist)
    datalist = report.get_analysis_result(CFG['归母净资产'], CFG['总负债'], CZ_NL.calc_chan_quan_bi_li)
    report.wirte_result("产权比例", 2,  datalist)
    # datalist = report.get_mao_jing_cha(CFG['营业总收入'], CFG['营业成本'], CFG['归母净利润'], YL_NL.calc_mao_jing_cha)
    # report.wirte_result("毛净差", 3,  datalist)
    datalist = report.get_analysis_result(CFG['归母净资产'], CFG['总资产'], CZ_NL.calc_quan_yi_cheng_shu)
    report.wirte_result("权益乘数", 4,  datalist)
    datalist = report.get_analysis_result(CFG['流动资产'], CFG['流动负债'], CZ_NL.calc_liu_dong_bi_li)
    report.wirte_result("流动比例", 5,  datalist)

    report.save_report()


      